// app/controller/sales_order_controller.js
'use strict';

const Controller = require('egg').Controller;
const fs = require('fs').promises;
const path = require('path');
const XLSX = require('xlsx');

class SalesOrderController extends Controller {
  // 获取销售订单列表
  async index() {
    const { ctx } = this;
    const { 
      page = 1, 
      limit, 
      pageSize, 
      orderNo,
      platformOrderId,
      customerId, 
      customerName,
      orderStatus, 
      invoiceStatus,
      platformSource,
      responsibleSalesman,
      startDate, 
      endDate,
      needCollection,
      sortField = 'order_date',
      sortOrder = 'desc'
    } = ctx.query;
    
    // 同时支持 limit 和 pageSize 参数
    const effectiveLimit = limit || pageSize || 20;
    
    try {
      // 构建查询条件
      const where = {};
      const include = [];
      
      // 添加组织过滤条件
      if (ctx.state.organizationId) {
        where.organizationId = ctx.state.organizationId;
      }
      
      if (orderNo) {
        where.orderNo = { [ctx.app.Sequelize.Op.like]: `%${orderNo}%` };
      }
      
      if (platformOrderId) {
        where.platformOrderId = { [ctx.app.Sequelize.Op.like]: `%${platformOrderId}%` };
      }
      
      if (customerId) {
        where.customerId = customerId;
      }
      
      if (orderStatus) {
        where.orderStatus = orderStatus;
      }
      
      if (invoiceStatus) {
        where.invoiceStatus = invoiceStatus;
      }
      
      if (platformSource) {
        where.platformSource = platformSource;
      }
      
      if (responsibleSalesman) {
        where.responsibleSalesman = responsibleSalesman;
      }
      
      if (needCollection === 'true') {
        where.collectionStatus = {
          [ctx.app.Sequelize.Op.in]: ['pending', 'contacted', 'difficult']
        };
      }
      
      if (startDate || endDate) {
        where.orderDate = {};
        if (startDate) {
          where.orderDate[ctx.app.Sequelize.Op.gte] = new Date(startDate);
        }
        if (endDate) {
          where.orderDate[ctx.app.Sequelize.Op.lte] = new Date(endDate);
        }
      }
      
      // 如果有客户名称查询条件，需要关联客户表
      if (customerName) {
        include.push({
          model: ctx.model.CustomerModel,
          as: 'customer',
          where: {
            name: { [ctx.app.Sequelize.Op.like]: `%${customerName}%` }
          },
          attributes: ['id', 'name'],
          required: true
        });
      } else {
        // 如果没有客户名称查询，仍然包含客户信息
        include.push({
          model: ctx.model.CustomerModel,
          as: 'customer',
          attributes: ['id', 'name']
        });
      }

      // 构建排序条件
      let order = [];
      if (sortField && sortOrder) {
        // 确保排序字段是安全的
        const allowedSortFields = ['order_date', 'order_amount', 'created_at'];
        const allowedSortOrders = ['asc', 'desc'];
        
        if (allowedSortFields.includes(sortField) && allowedSortOrders.includes(sortOrder.toLowerCase())) {
          order.push([sortField, sortOrder.toUpperCase()]);
        } else {
          // 默认排序
          order.push(['order_date', 'DESC']);
        }
      } else {
        // 默认排序
        order.push(['order_date', 'DESC']);
      }

      // 分页查询
      const offset = (parseInt(page) - 1) * parseInt(effectiveLimit);
      const { rows, count } = await ctx.model.SalesOrderModel.findAndCountAll({
        where,
        include,
        order,
        limit: parseInt(effectiveLimit),
        offset
      });

      // 转换数据格式，添加客户编码和联系人信息
      const ordersWithCustomerInfo = rows.map(order => {
        const orderData = order.toJSON();
        
        // 添加客户编码和联系人信息（如果可用）
        if (order.customer) {
          orderData.customerCode = order.customer.code || '';
          // 从联系人信息中提取第一个联系人作为主要联系人
          try {
            const contactPersons = JSON.parse(order.customer.contactPersons || '[]');
            if (contactPersons.length > 0) {
              orderData.primaryContact = contactPersons[0].name || '';
            }
          } catch (e) {
            // 解析联系人信息失败时忽略
            orderData.primaryContact = '';
          }
        }
        
        return orderData;
      });

      ctx.page(ordersWithCustomerInfo, count, parseInt(page), parseInt(effectiveLimit));
    } catch (error) {
      ctx.logger.error('获取销售订单列表失败:', error);
      ctx.error(`获取销售订单列表失败: ${error.message}`);
    }
  }
  
  // 获取销售订单详情
  async show() {
    const { ctx } = this;
    const { id } = ctx.params;
    
    try {
      const order = await ctx.model.SalesOrderModel.findByPk(id, {
        include: [{
          model: ctx.model.CustomerModel,
          as: 'customer'
        }]
      });
      
      if (!order) {
        ctx.error('销售订单不存在', 404);
        return;
      }
      
      ctx.success(order);
    } catch (error) {
      ctx.logger.error('获取销售订单详情失败:', error);
      ctx.error('获取销售订单详情失败');
    }
  }
  
  // 创建销售订单
  async create() {
    const { ctx } = this;
    const orderData = ctx.request.body;
    
    try {
      // 设置组织ID
      if (ctx.state.organizationId) {
        orderData.organizationId = ctx.state.organizationId;
      } else {
        orderData.organizationId = 1; // 默认组织ID
      }
      
      // 调用服务层方法
      const result = await ctx.service.salesOrderService.createOrder(orderData);
      
      ctx.success(result, '销售订单创建成功');
    } catch (error) {
      ctx.logger.error('创建销售订单失败:', error);
      ctx.error(`创建销售订单失败: ${error.message}`);
    }
  }
  
  // 更新销售订单
  async update() {
    const { ctx } = this;
    const { id } = ctx.params;
    const orderData = ctx.request.body;
    
    try {
      // 使用Joi验证服务进行参数验证
      // 首先验证ID
      const idValidation = ctx.service.validationService.validateId(parseInt(id));
      if (idValidation.error) {
        ctx.error(`参数验证失败: ${idValidation.error.message}`);
        return;
      }
      
      // 验证订单数据
      const validation = ctx.service.validationService.validateUpdateSalesOrder(orderData);
      if (validation.error) {
        ctx.error(`参数验证失败: ${validation.error.message}`);
        return;
      }
      
      const order = await ctx.model.SalesOrderModel.findByPk(id);
      
      if (!order) {
        ctx.error('销售订单不存在', 404);
        return;
      }
      
      await order.update(orderData);
      ctx.success(order, '销售订单更新成功');
    } catch (error) {
      ctx.logger.error('更新销售订单失败:', error);
      ctx.error('更新销售订单失败');
    }
  }
  
  // 删除销售订单
  async destroy() {
    const { ctx } = this;
    const { id } = ctx.params;
    
    try {
      const order = await ctx.model.SalesOrderModel.findByPk(id);
      
      if (!order) {
        ctx.error('销售订单不存在', 404);
        return;
      }
      
      await order.destroy();
      ctx.success(null, '销售订单删除成功');
    } catch (error) {
      ctx.logger.error('删除销售订单失败:', error);
      ctx.error('删除销售订单失败');
    }
  }
  
  // 导出销售订单
  async export() {
    const { ctx } = this;
    const { 
      orderStatus, 
      invoiceStatus, 
      platformSource, 
      startDate, 
      endDate,
      customerName
    } = ctx.query;
    
    try {
      // 构建查询条件
      const where = {};
      const include = [];
      
      // 添加组织过滤条件
      if (ctx.state.organizationId) {
        where.organizationId = ctx.state.organizationId;
      }
      
      if (orderStatus) {
        where.orderStatus = orderStatus;
      }
      
      if (invoiceStatus) {
        where.invoiceStatus = invoiceStatus;
      }
      
      if (platformSource) {
        where.platformSource = platformSource;
      }
      
      if (startDate || endDate) {
        where.orderDate = {};
        if (startDate) {
          where.orderDate[ctx.app.Sequelize.Op.gte] = new Date(startDate);
        }
        if (endDate) {
          where.orderDate[ctx.app.Sequelize.Op.lte] = new Date(endDate);
        }
      }
      
      // 如果有客户名称查询条件，需要关联客户表
      if (customerName) {
        include.push({
          model: ctx.model.CustomerModel,
          as: 'customer',
          where: {
            name: { [ctx.app.Sequelize.Op.like]: `%${customerName}%` }
          },
          required: true
        });
      }

      // 查询数据
      const orders = await ctx.model.SalesOrderModel.findAll({
        where,
        include,
        order: [['order_date', 'DESC']]
      });

      // 准备导出数据
      const exportData = orders.map(order => ({
        '订单编号': order.orderNo,
        '平台订单号': order.platformOrderId,
        '客户名称': order.customer ? order.customer.name : '',
        '订单金额': order.orderAmount,
        '订单状态': order.orderStatus,
        '开票状态': order.invoiceStatus,
        '平台来源': order.platformSource,
        '订单日期': order.orderDate,
        '创建时间': order.createdAt
      }));

      // 创建工作簿
      const workbook = XLSX.utils.book_new();
      const worksheet = XLSX.utils.json_to_sheet(exportData);
      XLSX.utils.book_append_sheet(workbook, worksheet, '销售订单');

      // 生成临时文件
      const tempDir = path.join(__dirname, '../../temp');
      await fs.mkdir(tempDir, { recursive: true });
      const tempFilePath = path.join(tempDir, `sales_orders_export_${Date.now()}.xlsx`);
      XLSX.writeFile(workbook, tempFilePath);

      // 设置响应头
      ctx.set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
      ctx.set('Content-Disposition', `attachment; filename="sales_orders_export_${Date.now()}.xlsx"`);

      // 发送文件
      ctx.body = fs.createReadStream(tempFilePath);
      
      // 文件发送完成后删除临时文件
      ctx.body.on('finish', async () => {
        try {
          await fs.unlink(tempFilePath);
        } catch (error) {
          ctx.logger.error('删除临时文件失败:', error);
        }
      });
    } catch (error) {
      ctx.logger.error('导出销售订单失败:', error);
      ctx.error('导出销售订单失败');
    }
  }
  
  // 获取销售订单统计信息
  async getStats() {
    const { ctx } = this;
    
    try {
      // 构建组织过滤条件
      const orgWhere = {};
      if (ctx.state.organizationId) {
        orgWhere.organizationId = ctx.state.organizationId;
      }

      // 获取总订单数
      const totalOrders = await ctx.model.SalesOrderModel.count({
        where: orgWhere
      });

      // 获取待处理订单数
      const pendingOrders = await ctx.model.SalesOrderModel.count({
        where: {
          ...orgWhere,
          orderStatus: 'pending'
        }
      });

      // 获取已完成订单数
      const completedOrders = await ctx.model.SalesOrderModel.count({
        where: {
          ...orgWhere,
          orderStatus: 'completed'
        }
      });

      // 获取问题订单数 (cancelled状态)
      const problemOrders = await ctx.model.SalesOrderModel.count({
        where: {
          ...orgWhere,
          orderStatus: 'cancelled'
        }
      });

      // 获取本月订单金额
      const currentDate = new Date();
      const firstDayOfMonth = new Date(currentDate.getFullYear(), currentDate.getMonth(), 1);
      const monthlyAmountResult = await ctx.model.SalesOrderModel.sum('orderAmount', {
        where: {
          ...orgWhere,
          orderDate: {
            [ctx.app.Sequelize.Op.gte]: firstDayOfMonth
          }
        }
      });
      const monthlyAmount = monthlyAmountResult || 0;

      // 计算平均订单金额
      const allOrders = await ctx.model.SalesOrderModel.findAll({
        where: orgWhere,
        attributes: [
          [ctx.app.Sequelize.fn('AVG', ctx.app.Sequelize.col('order_amount')), 'avgAmount']
        ],
        raw: true
      });
      const avgOrderAmount = allOrders[0]?.avgAmount ? parseFloat(allOrders[0].avgAmount) : 0;

      // 计算完成率
      const completionRate = totalOrders > 0 ? Math.round((completedOrders / totalOrders) * 10000) / 100 : 0;

      // 获取未开票订单数
      const uninvoicedOrders = await ctx.model.SalesOrderModel.count({
        where: {
          ...orgWhere,
          invoiceStatus: 'uninvoiced'
        }
      });

      // 计算趋势数据（与上个月比较）
      const lastMonth = new Date(currentDate.getFullYear(), currentDate.getMonth() - 1, 1);
      const lastMonthEnd = new Date(currentDate.getFullYear(), currentDate.getMonth(), 0);
      
      // 上月订单金额
      const lastMonthAmountResult = await ctx.model.SalesOrderModel.sum('orderAmount', {
        where: {
          ...orgWhere,
          orderDate: {
            [ctx.app.Sequelize.Op.gte]: lastMonth,
            [ctx.app.Sequelize.Op.lte]: lastMonthEnd
          }
        }
      });
      const lastMonthAmount = lastMonthAmountResult || 0;
      
      // 计算金额趋势
      const amountTrend = lastMonthAmount > 0 ? 
        Math.round(((monthlyAmount - lastMonthAmount) / lastMonthAmount) * 10000) / 100 : 
        (monthlyAmount > 0 ? 100 : 0);

      // 上月平均订单金额
      const lastMonthOrders = await ctx.model.SalesOrderModel.findAll({
        where: {
          ...orgWhere,
          orderDate: {
            [ctx.app.Sequelize.Op.gte]: lastMonth,
            [ctx.app.Sequelize.Op.lte]: lastMonthEnd
          }
        },
        attributes: [
          [ctx.app.Sequelize.fn('AVG', ctx.app.Sequelize.col('order_amount')), 'avgAmount']
        ],
        raw: true
      });
      const lastMonthAvgAmount = lastMonthOrders[0]?.avgAmount ? parseFloat(lastMonthOrders[0].avgAmount) : 0;
      
      // 计算平均订单金额趋势
      const avgTrend = lastMonthAvgAmount > 0 ? 
        Math.round(((avgOrderAmount - lastMonthAvgAmount) / lastMonthAvgAmount) * 10000) / 100 : 
        (avgOrderAmount > 0 ? 100 : 0);

      // 计算完成率趋势
      const lastMonthCompletedOrders = await ctx.model.SalesOrderModel.count({
        where: {
          ...orgWhere,
          orderStatus: 'completed',
          orderDate: {
            [ctx.app.Sequelize.Op.gte]: lastMonth,
            [ctx.app.Sequelize.Op.lte]: lastMonthEnd
          }
        }
      });
      const lastMonthTotalOrders = await ctx.model.SalesOrderModel.count({
        where: {
          ...orgWhere,
          orderDate: {
            [ctx.app.Sequelize.Op.gte]: lastMonth,
            [ctx.app.Sequelize.Op.lte]: lastMonthEnd
          }
        }
      });
      const lastMonthCompletionRate = lastMonthTotalOrders > 0 ? 
        Math.round((lastMonthCompletedOrders / lastMonthTotalOrders) * 10000) / 100 : 0;
      const completionTrend = lastMonthCompletionRate > 0 ? 
        Math.round((completionRate - lastMonthCompletionRate) * 100) / 100 : 
        (completionRate > 0 ? 100 : 0);

      // 计算开票率趋势
      const lastMonthUninvoicedOrders = await ctx.model.SalesOrderModel.count({
        where: {
          ...orgWhere,
          invoiceStatus: 'uninvoiced',
          orderDate: {
            [ctx.app.Sequelize.Op.gte]: lastMonth,
            [ctx.app.Sequelize.Op.lte]: lastMonthEnd
          }
        }
      });
      const lastMonthInvoicedRate = lastMonthTotalOrders > 0 ? 
        Math.round(((lastMonthTotalOrders - lastMonthUninvoicedOrders) / lastMonthTotalOrders) * 10000) / 100 : 0;
      const currentInvoicedRate = totalOrders > 0 ? 
        Math.round(((totalOrders - uninvoicedOrders) / totalOrders) * 10000) / 100 : 0;
      const invoiceTrend = lastMonthInvoicedRate > 0 ? 
        Math.round((currentInvoicedRate - lastMonthInvoicedRate) * 100) / 100 : 
        (currentInvoicedRate > 0 ? 100 : 0);

      ctx.success({
        totalOrders,
        pendingOrders,
        completedOrders,
        problemOrders,
        monthlyAmount,
        avgOrderAmount,
        completionRate,
        uninvoicedOrders,
        amountTrend,
        avgTrend,
        completionTrend,
        invoiceTrend
      });
    } catch (error) {
      ctx.logger.error('获取销售订单统计信息失败:', error);
      ctx.error('获取销售订单统计信息失败');
    }
  }
  
  // 批量更新订单状态
  async batchUpdateStatus() {
    const { ctx } = this;
    const { ids, status } = ctx.request.body;
    
    try {
      // 验证参数
      if (!Array.isArray(ids) || ids.length === 0) {
        ctx.error('请选择要更新的订单');
        return;
      }
      
      if (!status) {
        ctx.error('请选择要更新的状态');
        return;
      }
      
      // 验证状态值
      const allowedStatuses = ['pending', 'paid', 'shipped', 'completed', 'cancelled'];
      if (!allowedStatuses.includes(status)) {
        ctx.error('无效的订单状态');
        return;
      }
      
      // 批量更新
      const [updatedCount] = await ctx.model.SalesOrderModel.update(
        { orderStatus: status },
        { where: { id: ids } }
      );
      
      ctx.success({ updatedCount }, `成功更新${updatedCount}个订单的状态`);
    } catch (error) {
      ctx.logger.error('批量更新订单状态失败:', error);
      ctx.error('批量更新订单状态失败');
    }
  }
  
  // 批量删除订单
  async batchDelete() {
    const { ctx } = this;
    const { ids } = ctx.request.body;
    
    try {
      // 验证参数
      if (!Array.isArray(ids) || ids.length === 0) {
        ctx.error('请选择要删除的订单');
        return;
      }
      
      // 批量删除
      const deletedCount = await ctx.model.SalesOrderModel.destroy({
        where: { id: ids }
      });
      
      ctx.success({ deletedCount }, `成功删除${deletedCount}个订单`);
    } catch (error) {
      ctx.logger.error('批量删除订单失败:', error);
      ctx.error('批量删除订单失败');
    }
  }
  
  // 更新订单催款状态
  async updateCollectionStatus() {
    const { ctx } = this;
    const { id } = ctx.params;
    const { collectionStatus, collectionResult, nextFollowupDate } = ctx.request.body;
    
    try {
      // 参数验证
      const validation = ctx.service.validationService.validateUpdateCollectionRecord({
        collectionStatus,
        responseResult: collectionResult,
        nextFollowupDate
      });
      
      if (validation.error) {
        ctx.error(`参数验证失败: ${validation.error.message}`);
        return;
      }
      
      // 查找订单
      const order = await ctx.model.SalesOrderModel.findByPk(id);
      
      if (!order) {
        ctx.error('订单不存在', 404);
        return;
      }
      
      // 准备更新数据
      const updateData = {
        collectionStatus: collectionStatus || order.collectionStatus,
        lastCollectionDate: new Date(),
        collectionCount: (order.collectionCount || 0) + 1
      };
      
      // 如果提供了催款结果，更新最后催款结果
      if (collectionResult !== undefined) {
        updateData.lastCollectionResult = collectionResult;
      }
      
      // 如果提供了下次跟进日期，更新下次跟进日期
      if (nextFollowupDate !== undefined) {
        updateData.nextFollowupDate = nextFollowupDate ? new Date(nextFollowupDate) : null;
      }
      
      // 更新订单
      await order.update(updateData);
      
      ctx.success(order, '催款状态更新成功');
    } catch (error) {
      ctx.logger.error('更新催款状态失败:', error);
      ctx.error(`更新催款状态失败: ${error.message}`);
    }
  }
  
  // 导入销售订单
  async importSalesOrders() {
    const { ctx } = this;
    const file = ctx.request.files[0];
    const fieldMapping = ctx.request.body.fieldMapping ? JSON.parse(ctx.request.body.fieldMapping) : {};
    
    try {
      // 验证文件
      if (!file) {
        ctx.error('请选择要导入的文件');
        return;
      }
      
      // 调用服务层方法
      const result = await ctx.service.salesOrderService.importOrders(file, fieldMapping);
      
      ctx.success(result, `销售订单导入成功，共处理${result.processedCount}条记录`);
    } catch (error) {
      ctx.logger.error('导入销售订单失败:', error);
      ctx.error(`导入销售订单失败: ${error.message}`);
    }
  }
  
  // 导出销售订单模板
  async exportTemplate() {
    const { ctx } = this;
    
    try {
      const XLSX = require('xlsx');
      
      // 创建工作簿
      const workbook = XLSX.utils.book_new();
      
      // 创建模板数据
      const templateData = [
        {
          '订单编号': 'SO20231001001',
          '平台订单号': 'PO20231001001',
          '客户名称': '示例客户',
          '订单金额': 1000.00,
          '订单状态': 'completed',
          '开票状态': 'invoiced',
          '平台来源': 'zcy',
          '订单日期': '2023-10-01',
          '负责销售': '张三'
        }
      ];
      
      // 创建工作表
      const worksheet = XLSX.utils.json_to_sheet(templateData);
      
      // 添加工作表到工作簿
      XLSX.utils.book_append_sheet(workbook, worksheet, '销售订单导入模板');
      
      // 写入缓冲区
      const buffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' });
      
      // 设置响应头
      ctx.set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
      ctx.set('Content-Disposition', 'attachment; filename=sales_orders_import_template.xlsx');
      
      // 直接返回buffer，不使用标准响应格式
      ctx.body = buffer;
    } catch (error) {
      ctx.logger.error('生成导入模板失败:', error);
      ctx.error(`生成导入模板失败: ${error.message}`);
    }
  }
}

module.exports = SalesOrderController;